my_fire_db <- dbConnect(RSQLite::SQLite(), "FPA_FOD_20170508.sqlite")
dbListTables(my_fire_db)
##  [1] "ElementaryGeometries"               "Fires"                             
##  [3] "KNN"                                "NWCG_UnitIDActive_20170109"        
##  [5] "SpatialIndex"                       "geom_cols_ref_sys"                 
##  [7] "geometry_columns"                   "geometry_columns_auth"             
##  [9] "geometry_columns_field_infos"       "geometry_columns_statistics"       
## [11] "geometry_columns_time"              "idx_Fires_Shape"                   
## [13] "idx_Fires_Shape_node"               "idx_Fires_Shape_parent"            
## [15] "idx_Fires_Shape_rowid"              "spatial_ref_sys"                   
## [17] "spatial_ref_sys_all"                "spatial_ref_sys_aux"               
## [19] "spatialite_history"                 "sql_statements_log"                
## [21] "sqlite_sequence"                    "vector_layers"                     
## [23] "vector_layers_auth"                 "vector_layers_field_infos"         
## [25] "vector_layers_statistics"           "views_geometry_columns"            
## [27] "views_geometry_columns_auth"        "views_geometry_columns_field_infos"
## [29] "views_geometry_columns_statistics"  "virts_geometry_columns"            
## [31] "virts_geometry_columns_auth"        "virts_geometry_columns_field_infos"
## [33] "virts_geometry_columns_statistics"
dbListFields(my_fire_db, "Fires")
##  [1] "OBJECTID"                   "FOD_ID"                    
##  [3] "FPA_ID"                     "SOURCE_SYSTEM_TYPE"        
##  [5] "SOURCE_SYSTEM"              "NWCG_REPORTING_AGENCY"     
##  [7] "NWCG_REPORTING_UNIT_ID"     "NWCG_REPORTING_UNIT_NAME"  
##  [9] "SOURCE_REPORTING_UNIT"      "SOURCE_REPORTING_UNIT_NAME"
## [11] "LOCAL_FIRE_REPORT_ID"       "LOCAL_INCIDENT_ID"         
## [13] "FIRE_CODE"                  "FIRE_NAME"                 
## [15] "ICS_209_INCIDENT_NUMBER"    "ICS_209_NAME"              
## [17] "MTBS_ID"                    "MTBS_FIRE_NAME"            
## [19] "COMPLEX_NAME"               "FIRE_YEAR"                 
## [21] "DISCOVERY_DATE"             "DISCOVERY_DOY"             
## [23] "DISCOVERY_TIME"             "STAT_CAUSE_CODE"           
## [25] "STAT_CAUSE_DESCR"           "CONT_DATE"                 
## [27] "CONT_DOY"                   "CONT_TIME"                 
## [29] "FIRE_SIZE"                  "FIRE_SIZE_CLASS"           
## [31] "LATITUDE"                   "LONGITUDE"                 
## [33] "OWNER_CODE"                 "OWNER_DESCR"               
## [35] "STATE"                      "COUNTY"                    
## [37] "FIPS_CODE"                  "FIPS_NAME"                 
## [39] "Shape"
dbListFields(my_fire_db, "vector_layers")
## [1] "layer_type"            "table_name"            "geometry_column"      
## [4] "geometry_type"         "coord_dimension"       "srid"                 
## [7] "spatial_index_enabled"
fire_data <- dbReadTable(my_fire_db,"fires")
fire_data_geometry <- dbReadTable(my_fire_db,"vector_layers")
# To query database...
# Eldo_fires <- dbGetQuery(my_fire_db, 'SELECT * FROM fires WHERE "SOURCE_REPORTING_UNIT_NAME" == "Eldorado National Forest"')
# 
# #only look at montana and selecting only a few columns
# mt <- dbGetQuery(my_fire_db, 'SELECT * FROM fires WHERE "STATE" == "MT"') %>% 
#   clean_names() %>% 
#   dplyr::select(fire_name, fire_year, stat_cause_descr, fire_size, latitude, longitude, state, county, source_reporting_unit_name)


# year_2015_tidy <- year_2015 %>% 
#   clean_names() %>% 
#   select(fire_name, fire_year, stat_cause_descr, fire_size, latitude, longitude, state, county, shape)
#get montana state and counties shapefile 
counties_mt <- us_counties(states = "Montana")
plot(st_geometry(counties_mt))

mt <- read_csv("montana_fire.csv") %>% 
  clean_names() %>% 
  select(-fire_code, -fire_size_class)
## Parsed with column specification:
## cols(
##   FIRE_CODE = col_character(),
##   FIRE_NAME = col_character(),
##   SOURCE_REPORTING_UNIT_NAME = col_character(),
##   FIRE_YEAR = col_double(),
##   DISCOVERY_DOY = col_double(),
##   DISCOVERY_TIME = col_character(),
##   CONT_DOY = col_double(),
##   CONT_TIME = col_character(),
##   STAT_CAUSE_DESCR = col_character(),
##   FIRE_SIZE = col_double(),
##   FIRE_SIZE_CLASS = col_character(),
##   OWNER_DESCR = col_character(),
##   STATE = col_character(),
##   LATITUDE = col_double(),
##   LONGITUDE = col_double()
## )
mt_sf <- st_as_sf(mt, coords = c("longitude", "latitude"), crs = 4326)
#considering exporting mt dataframe to make .csv and read in as read_sf --> thoughts?
# ------------------------------------------------------------------------------------------------
#MAIN GOAL IS TO MAKE INTERACTIVE OR AT LEAST USE TMAP
# ------------------------------------------------------------------------------------------------
#every montana fire layered over a map of montana
ggplot()+
  geom_sf(data = counties_mt)+
  geom_point(data = mt, aes (x = longitude, y = latitude),
             alpha = 0.5)

#filter by cause from widget
mt_cause <- mt_sf %>% 
  filter(stat_cause_descr == "Campfire")

# ggplot()+
#   geom_sf(data = counties_mt)+
#   geom_point(data = mt_cause, aes (x = longitude, y = latitude),
#              alpha = 0.5)

#filter by time from widget (have user pick a specific year)
mt_year <- mt %>% 
  filter(fire_year == "2005")

ggplot()+
  geom_sf(data = counties_mt)+
  geom_point(data = mt_year, aes (x = longitude, y = latitude),
             alpha = 0.5)

#filter to search specific fire name in montana
mt_name <- mt %>% 
  filter(fire_name == "GATEWAY")

ggplot()+
  geom_sf(data = counties_mt)+
  geom_point(data = mt_name, aes (x = longitude, y = latitude))

mt_sf2 <- mt_sf %>% 
  select(fire_size)
#ask about how to have Esri.World Image locked only on MT and have the outline of MT
#ask how to add a gradient of colors for the dots to show difference in fire_area
tmap_mode("view")
## tmap mode set to interactive viewing
map <- tm_basemap("Stamen.TerrainBackground")+
  tm_shape(mt_sf2)+
  tm_dots(label = "fire_size", col = "skyblue",
          size = 0.02)

map
tmap_mode("view")
## tmap mode set to interactive viewing
cause_map <- tm_basemap("Stamen.TerrainBackground")+
  tm_shape(mt_cause)+
  tm_dots(label = "Campfire", col = "orange",
          size = 0.02)

cause_map

###Next step:

#containment time
mt_time <- fire_data %>% 
  clean_names() %>% 
  filter(state == "MT") %>% 
  select(fire_name, fire_year, discovery_date, discovery_time, cont_date, cont_time, source_reporting_unit_name)

#change format of discovery date and continuous date from julian to normal
mt_date <- mt_time %>% 
  mutate(new_dis_date = as.Date(discovery_date, origin = structure(-2440588, class = "Date"))) %>% 
  mutate(new_cont_date = as.Date(cont_date, origin = structure(-2440588, class = "Date")))

#dat$date_time = mdy_hm(paste(dat$date, dat$time))

library(lubridate)
## 
## Attaching package: 'lubridate'
## The following object is masked from 'package:base':
## 
##     date
mt_date_combined <- mt_date %>% 
  mutate(final_dis_date = lubridate::ymd_hm(paste(mt_date$new_dis_date, mt_date$discovery_time))) %>% 
  mutate(final_cont_date = lubridate::ymd_hm(paste(mt_date$new_cont_date, mt_date$cont_time))) %>% 
  mutate(interval = difftime(final_cont_date,final_dis_date,units = "min")) %>% 
  mutate(interval = round(interval/60, 2)) %>% 
  drop_na(final_cont_date, final_dis_date)
## Warning: 3482 failed to parse.
## Warning: 4122 failed to parse.
mt_date_park <- mt_date_combined %>% 
  filter(source_reporting_unit_name == "Kootenai National Forest")

mt_interval_avg <- mt_date_park %>% 
  group_by(fire_year) %>% 
  summarize(
    mean_interval = round(mean(interval),2)
  )

ggplot(data = mt_interval_avg, aes(x = fire_year, y = mean_interval))+
  geom_line(color = "darkgreen") +
  geom_smooth(color = "black",
              size = 0.2,
              linetype = "dashed",
              fill = "lightgreen",
              alpha = 0.2) +
  theme_minimal()
## Don't know how to automatically pick scale for object of type difftime. Defaulting to continuous.
## `geom_smooth()` using method = 'loess' and formula 'y ~ x'

library(gt)

mt_name %>%  
  gt() %>% 
  tab_header(
    title = "Fire Summary"
  ) %>% 
  cols_label(
    fire_name = "Name of Fire",
    fire_year = "Year",
    stat_cause_descr = "Cause of Fire",
    fire_size = "Fire Area",
    latitude = "Latitude",
    longitude = "Longitude",
    state = "State",
    source_reporting_unit_name = "Name of Reporting Source"
  )
Fire Summary
Name of Fire Name of Reporting Source Year discovery_doy discovery_time cont_doy cont_time Cause of Fire Fire Area owner_descr State Latitude Longitude
GATEWAY Kootenai National Forest 2005 65 1510 65 1900 Campfire 9.0 USFS MT 48.99833 -115.1656
GATEWAY Kootenai National Forest 2008 117 1357 117 2000 Debris Burning 0.6 STATE OR PRIVATE MT 48.99944 -115.0903
GATEWAY Fort Peck Agency 1999 210 1820 210 1840 Smoking 1.0 BIA MT 48.10780 -105.1972